﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MixScript.BLL;
using MixScript.BLL.Accounting;
using System.Globalization;

namespace MixScript.BLL.FI
{
    public class cAccHeader : cBaseBLL
    {

        public cAccHeader() { }


        public bool ListJournal(out DataTable dt, int year, int month, int type)
        {
            String Query = "AND h.sliptype ='{2}'";
            if (type == 2)
                Query = "AND (h.sliptype ='{2}' OR h.sliptype ='4')";
            if (type == 3)
                Query = "AND (h.sliptype ='{2}' OR h.sliptype ='5')";


            string Sql;
            Sql = @"SELECT h.slipid,h.slipdate as slipdate,h.slipcode,h.slipnum,h.slipcurr,
                    h.slipdesc,ISNULL(d.Debet,0) Debet,ISNULL(d.Kredit,0) Kredit,h.posting,ISNULL(matref,'') matref
                    FROM accsliphdr h LEFT JOIN  (
                    SELECT header_id,
                    sum(case dbcd when 'D' tHEN ISNULL(valconvert,0) end) as Debet,
                    sum(case dbcd when 'C' tHEN ISNULL(valconvert,0) end) as Kredit FROM accslipdet group by header_id
                    ) d ON h.slipid=d.header_id 
                    WHERE  year(h.slipdate) = '{0}' AND month(h.slipdate) = '{1}' " + Query;
            try
            {
                dt = Dbserver.ExecuteQuery(string.Format(Sql, year, month, type), "Journal");
                return true;
            }
            catch (Exception ex)
            { throw ex; }
        }

        public string getMaxJournalNo(string no, int year)
        {
            string sql;
            sql = @"select CASE LEN(ISNULL(Max(slipnum),0))
                    WHEN 6 THEN '0'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 5 THEN '00'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 4 THEN '000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 3 THEN '0000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 2 THEN '00000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 1 THEN '000000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    ELSE ISNULL(Max(slipnum),0)
                    END from accsliphdr 
                    where slipcode Like '{0}%' and YEAR(slipdate) = {1}";

            try
            {
                return (string)Dbserver.ExecuteScalar(string.Format(sql, no, year));
            }
            catch (Exception ex)
            { throw ex; }
        }
        public bool deleteJournal(string slipid)
        {
            Dbserver.openConn();
            Dbserver.ExecuteNonQuery(String.Format("Delete from accslipdet where header_id ='{0}'", slipid));
            Dbserver.CommitAndCloseConn();
            Dbserver.openConn();
            Dbserver.ExecuteNonQuery(String.Format("Delete from accsliphdr where slipid ='{0}'", slipid));
            Dbserver.CommitAndCloseConn();
            return true;
        }
        public bool updateJournal(string slipid, string slipcode, string slipnum, string slipcurr, string slipdesc, DateTime slipdate, ListGeneralJournal journals
            , string ddlCashAccount, string ddlCashCurrency, string kurs, string txtCashDescripiton, string txtCashRate,
            string txtCashCredit, string txtCashConvert, string ddlBank)
        {

            String sDate;
            string sql;
            int id;
            string subsql;
            string dbcd = "";
            subsql = @"INSERT INTO accslipdet (header_id,acctnum,
                    [desc],dbcd,currency,kurs,rate,amount,valconvert,invoice,process,subsidiary,sub_date,sub_terms,so_id)
                    VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',0)";


            sql = @"UPDATE accsliphdr SET slipcode='{0}',slipnum='{1}',slipcurr='{2}',slipdesc='{3}',slipdate='{4}' 
                    WHERE slipid = '{5}'";


            if (ddlCashAccount == null)
            {
                if (slipcode.ToLower().Contains("p"))
                    dbcd = "C";
                if (slipcode.ToLower().Contains("r"))
                    dbcd = "D";
                Dbserver.openConn();
                sDate = DBNull.Value.ToString();
                Dbserver.ExecuteNonQuery(String.Format(subsql,
                    slipid,
                    ddlCashAccount,
                    txtCashDescripiton,
                    dbcd,
                    ddlCashCurrency,
                    kurs,
                    txtCashRate,
                    txtCashCredit,
                    txtCashConvert,
                    DBNull.Value.ToString(),
                    DBNull.Value.ToString(),
                    DBNull.Value.ToString(),
                    sDate,
                    DBNull.Value.ToString()));
                Dbserver.CommitAndCloseConn();
            }

            Dbserver.ExecuteScalar(string.Format(sql, slipcode, slipnum, slipcurr, slipdesc, String.Format("{0:yyyy-MM-dd}", slipdate), slipid));

            Dbserver.openConn();
            Dbserver.ExecuteNonQuery(String.Format("Delete from accslipdet where header_id ='{0}'", slipid));
            Dbserver.CommitAndCloseConn();
            for (int i = 0; i < journals.ListOfJournal.Count; i++)
            {

                subsql = @"INSERT INTO accslipdet (header_id,acctnum,
                    [desc],dbcd,currency,kurs,rate,amount,valconvert,invoice,process,subsidiary,sub_date,sub_terms,so_id)
                    VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',0)";
                string val;
                if (journals.ListOfJournal[i].debit != 0)
                {
                    dbcd = "D";
                    val = journals.ListOfJournal[i].debit.ToString();
                }
                else
                {
                    dbcd = "C";
                    val = journals.ListOfJournal[i].credit.ToString();
                }
                Dbserver.openConn();
                if (journals.ListOfJournal[i].invoiceDate != DateTime.MinValue)
                    sDate = String.Format("{0:yyyy-MM-dd}", journals.ListOfJournal[i].invoiceDate);
                else
                    sDate = DBNull.Value.ToString();
                Dbserver.ExecuteNonQuery(String.Format(subsql,
                    slipid,
                    journals.ListOfJournal[i].acct,
                    journals.ListOfJournal[i].description,
                    dbcd,
                    journals.ListOfJournal[i].currency,
                    journals.ListOfJournal[i].kurs,
                    journals.ListOfJournal[i].rate,
                    journals.ListOfJournal[i].ammount,
                    val,
                    journals.ListOfJournal[i].invoice,
                    journals.ListOfJournal[i].process,
                    journals.ListOfJournal[i].subsidiary,
                    sDate,
                    journals.ListOfJournal[i].terms));
                Dbserver.CommitAndCloseConn();

            }



            return true;

        }
        public bool saveJournal(String slipcode, string slipnum, string slipcurr, string slipdesc, DateTime slipdate, ListGeneralJournal journals,
            string ddlCashAccount, string ddlCashCurrency, string kurs, string txtCashDescripiton, string txtCashRate,
            string txtCashCredit, string txtCashConvert, string ddlBank)
        {
            String sDate;
            string sql;
            int id;
            string subsql;
            string dbcd = "";
            subsql = @"INSERT INTO accslipdet (header_id,acctnum,
                    [desc],dbcd,currency,kurs,rate,amount,valconvert,invoice,process,subsidiary,sub_date,sub_terms,so_id,poinvoiceno,poinvoicedate)
                    VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',0,'{14}','{15}')";
            sql = @"InsertJurnalHeader '{0}','{1}','{2}','{3}','{4}'";

            id = Convert.ToInt32(Dbserver.ExecuteScalar(string.Format(sql, slipcode, slipnum, slipcurr, slipdesc, String.Format("{0:yyyy-MM-dd}", slipdate))));

            if (ddlCashAccount != null)
            {
                if (slipcode.ToLower().Contains("p"))
                    dbcd = "C";
                if (slipcode.ToLower().Contains("r"))
                    dbcd = "D";
                Dbserver.openConn();
                sDate = DBNull.Value.ToString();
                Dbserver.ExecuteNonQuery(String.Format(subsql,
                    id,
                    ddlCashAccount.Trim(),
                    txtCashDescripiton,
                    dbcd,
                    ddlCashCurrency.Trim(),
                    kurs,
                    txtCashRate.Trim(),
                    txtCashCredit.Trim(),
                    txtCashConvert.Trim(),
                    0,
                    0,
                    0,
                    sDate,
                    0, "", ""));
                Dbserver.CommitAndCloseConn();
            }


            for (int i = 0; i < journals.ListOfJournal.Count; i++)
            {



                string val;
                if (journals.ListOfJournal[i].debit != 0)
                {
                    dbcd = "D";
                    val = journals.ListOfJournal[i].debit.ToString();
                }
                else
                {
                    dbcd = "C";
                    val = journals.ListOfJournal[i].credit.ToString();
                }
                Dbserver.openConn();
                if (journals.ListOfJournal[i].invoiceDate != DateTime.MinValue)
                    sDate = String.Format("{0:yyyy-MM-dd}", journals.ListOfJournal[i].invoiceDate);
                else
                    sDate = DBNull.Value.ToString();

                string sdate = "";
                if (journals.ListOfJournal[i].poInvoiceDate != DateTime.MinValue)
                    sDate = journals.ListOfJournal[i].poInvoiceDate.ToShortDateString();

                Dbserver.ExecuteNonQuery(String.Format(subsql,
                    id,
                    journals.ListOfJournal[i].acct,
                    journals.ListOfJournal[i].description,
                    dbcd,
                    journals.ListOfJournal[i].currency,
                    journals.ListOfJournal[i].kurs,
                    journals.ListOfJournal[i].rate,
                    journals.ListOfJournal[i].ammount,
                    val,
                    journals.ListOfJournal[i].invoice,
                    journals.ListOfJournal[i].process,
                    journals.ListOfJournal[i].subsidiary,
                    sDate,
                    journals.ListOfJournal[i].terms,
                    journals.ListOfJournal[i].poInvoice,
                    sDate
                    ));
                Dbserver.CommitAndCloseConn();

            }



            return true;
        }

        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool loadJournalhdr(string id, out DataTable dt)
        {
            try
            {
                dt = Dbserver.ExecuteQuery(string.Format("select * from accsliphdr where slipid ={0}", id), "accheader");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public bool getAccByProcess(out DataTable dt, String process)
        {
            try
            {
                dt = Dbserver.ExecuteQuery(string.Format("SELECT acctnum,acctnum +' - '+ acctname  acctname FROM accaccounts a WHERE process ='{0}'", process), "accbyproccess");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="posting"></param>
        /// <param name="slipType"></param>
        /// <param name="dateFrom"></param>
        /// <param name="dateTo"></param>
        /// <returns></returns>
        /// 
        public bool getPosting(out DataTable dt, Int32 posting, Int32 slipType, DateTime dateFrom, DateTime dateTo)
        {
            string sType = "";
            if (slipType != 0)
                sType = "sliptype = {" + slipType + "} AND ";
            string sSql = @"SELECT slipid,cast(slipdate as NVARcHAR(11)) AS slipdatedmy,slipcode,slipnum,slipcurr,
            slipdesc FROM accsliphdr
            WHERE posting = {0}  AND {1}
            slipdate BETWeen '{2}' AND '{3}'
            ORDER BY slipdate,sliptype DESC";
            try
            {
                dt = Dbserver.ExecuteQuery(string.Format(sSql, posting, sType, string.Format("{0:yyyy-MM-dd}", dateFrom.ToString()), string.Format("{0:yyyy-MM-dd}", dateTo.ToString())), "Posting");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="posting"></param>
        /// <param name="slipType"></param>
        /// <param name="dateFrom"></param>
        /// <param name="dateTo"></param>
        /// <returns></returns>

        public bool ExecutePosting(Int32 slipID, bool isPosting)
        {
            string sSql = @" accsliphdr
            SET posting = {0}  where slipid = '{1}'";
            try
            {
                return Dbserver.ExecuteNonQuery(string.Format(sSql, slipID, Convert.ToInt32(isPosting)));

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public DataTable loadJournalHeader(string id)
        {
            DataTable dt = new DataTable();
            GeneralJournal gj;
            ListGeneralJournal gjss = new ListGeneralJournal();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format(@"select slipdate ,slipcode +'-'+ slipnum slipno,slipcurr, slipdesc  
                                                        from accsliphdr  where slipid = {0}", id), "accheader");
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


        }
        public DataTable loadJournalHeaderGRN(string id)
        {
            DataTable dt = new DataTable();
            ListGeneralJournal gjss = new ListGeneralJournal();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format(@"select slipcode +'-'+ slipnum slipno,matref,slipdate , 
                                                            MAX(det.subsidiary) subsidiary,ISNULL(Max(V.bpidnm),'-')  Customer,CONVERT(varchar, CONVERT(money, SUM(det.amount) ), 1)  ammount
                                                            from accsliphdr  hdr inner join accslipdet det on 
                                                            hdr.slipid  = det.header_id 
                                                            left join vw_joid_customer_v V ON V.codfbpid = det.subsidiary                                                            
                                                            where slipid = {0} group by slipdate ,slipcode ,slipnum ,matref", id), "accheader");
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


        }
        public DataTable loadJournalDetailGRN(string id)
        {
            DataTable dt = new DataTable();
            GeneralJournal gj;
            ListGeneralJournal gjss = new ListGeneralJournal();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format(@"select  a.[desc],a.rate,amount ,a.currency, a.acctnum,a.amount,
                                                        CASE a.dbcd WHEN 'D' THEN a.valconvert WHEN 'G' THEN valconvert END As DEBIT,
                                                        CASE a.dbcd WHEN 'C' THEN valconvert END AS KREDIT,v.bpidnm  subsidiary ,invoice ,sub_date invoiceDate ,process 
												        from accslipdet a 
												        left join vw_joid_customer_v v ON v.codfbpid = a.subsidiary
                                                        where header_id ={0}", id), "accheader");
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


        }
        public DataTable loadJournalDetail(string id)
        {
            DataTable dt = new DataTable();
            GeneralJournal gj;
            ListGeneralJournal gjss = new ListGeneralJournal();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format(@"select  a.[desc],a.rate,amount ,a.currency, a.acctnum,a.amount,CASE a.dbcd WHEN 'D' THEN a.valconvert WHEN 'G' THEN valconvert END As DEBIT,
                                                CASE a.dbcd WHEN 'C' THEN valconvert END AS KREDIT,subsidiary ,invoice ,sub_date invoiceDate ,process 
                                                 from accslipdet a where header_id ={0}", id), "accheader");
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


        }

        /// <summary>
        /// cAccDetail
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public ListGeneralJournal loadJournalsub(string id)
        {
            DataTable dt = new DataTable();
            GeneralJournal gj;
            ListGeneralJournal gjss = new ListGeneralJournal();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format("select * from accslipdet where header_id ={0}", id), "accheader");
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    gj = new GeneralJournal();
                    gj.no = i + 1;
                    gj.id = i + 1;
                    int acct;
                    int.TryParse(dt.Rows[i].ItemArray[2].ToString(), out  acct);
                    gj.acct = acct;
                    gj.description = dt.Rows[i].ItemArray[3].ToString();
                    if (dt.Rows[i].ItemArray[4].ToString() == "C")
                    {
                        int val;
                        int.TryParse(dt.Rows[i].ItemArray[9].ToString(), out val);
                        gj.debit = val;
                        gj.credit = 0;
                    }
                    else
                    {
                        int val;
                        int.TryParse(dt.Rows[i].ItemArray[9].ToString(), out val);
                        gj.credit = val;
                        gj.debit = 0;
                    }
                    gj.currency = dt.Rows[i].ItemArray[5].ToString();
                    gj.kurs = dt.Rows[i].ItemArray[6].ToString();
                    float rate;
                    float.TryParse(dt.Rows[i].ItemArray[7].ToString(), out rate);
                    gj.rate = (int)rate;
                    float ammount;
                    float.TryParse(dt.Rows[i].ItemArray[8].ToString(), out ammount);
                    gj.ammount = (int)ammount;
                    float invoice;
                    float.TryParse(dt.Rows[i].ItemArray[12].ToString(), out invoice);
                    gj.invoice = (int)invoice;
                    float process;
                    float.TryParse(dt.Rows[i].ItemArray[13].ToString(), out process);
                    gj.process = (int)process;
                    gj.subsidiary = dt.Rows[i].ItemArray[14].ToString();
                    gj.invoiceDate = DateTime.Parse(dt.Rows[i].ItemArray[15].ToString());
                    gj.terms = dt.Rows[i].ItemArray[16].ToString();
                    if (gjss.ListOfJournal == null)
                        gjss.ListOfJournal = new List<GeneralJournal>();
                    gjss.ListOfJournal.Add(gj);

                }

                return gjss;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}
